using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.Common;

using UMEVOR.DTO;

using Microsoft.Practices.EnterpriseLibrary.Data;

namespace UMEVOR.DAL
{
    public class DALT_PALESTRA : DALConnection
    {

        #region Incluir

        public void incluir(T_PALESTRA obj)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();

                SQL.Append(" INSERT INTO T_PALESTRA ");
                SQL.Append("    (ID_CASA_ESPIRITA, DT_PALESTRA, HR_PALESTRA, TX_TEMA, TX_ORADOR) ");
                SQL.Append(" VALUES ");
                SQL.Append("    ("+ obj.ID_CASA_ESPIRITA.ToString() + ", str_to_date('" + obj.DT_PALESTRA.ToString("dd/MM/yyyy") + "', '%d/%m/%Y'), ");
                SQL.Append("    time('" + obj.HR_PALESTRA.ToString("HH:mm:ss") + "'), '" + obj.TX_TEMA + " ', '" + obj.TX_ORADOR + " ') ");

                DbCommand insertT_PALESTRA = database.GetSqlStringCommand(SQL.ToString());

                insertT_PALESTRA.CommandText = SQL.ToString();

                database.ExecuteNonQuery(insertT_PALESTRA, DALConnection.transaction);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Incluir[]

        public void incluir(T_PALESTRA[] obj)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();
                DbCommand[] insertT_PALESTRA = new DbCommand[obj.Length];

                for (int index = 0; index < obj.Length; index++)
                {
                    T_PALESTRA o = obj[index];
                    SQL = new StringBuilder();

                    SQL.Append(" INSERT INTO T_PALESTRA ");
                    SQL.Append("    (ID_CASA_ESPIRITA, DT_PALESTRA, TX_TEMA, TX_ORADOR) ");
                    SQL.Append(" VALUES ");
                    SQL.Append("    (" + o.ID_CASA_ESPIRITA.ToString() + ", str_to_date('" + o.DT_PALESTRA.ToString("dd/MM/yyyy") + "', '%d/%m/%Y'), ");
                    SQL.Append("    time('" + o.HR_PALESTRA.ToString("HH:mm:ss") + "'), '" + o.TX_TEMA + " ', '" + o.TX_ORADOR + " ') ");

                    insertT_PALESTRA[index].CommandText = SQL.ToString();
                }

                try
                {
                    foreach (DbCommand cmd in insertT_PALESTRA)
                    {
                        database.ExecuteNonQuery(cmd, DALConnection.transaction);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Alterar

        public void alterar(T_PALESTRA obj)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();

                SQL.Append(" UPDATE T_PALESTRA SET ");
                SQL.Append("    ID_CASA_ESPIRITA =  " + obj.ID_CASA_ESPIRITA.ToString() + ", ");
                SQL.Append("    DT_PALESTRA = str_to_date('" + obj.DT_PALESTRA.ToString("dd/MM/yyyy") + "', '%d/%m/%Y') , ");
                SQL.Append("    HR_PALESTRA = time('" + obj.HR_PALESTRA.ToString("HH:mm:ss") + "') , ");
                SQL.Append("    TX_TEMA = '" + obj.TX_TEMA + " ' , ");
                SQL.Append("    TX_ORADOR = '" + obj.TX_ORADOR + " ' ");
                SQL.Append(" WHERE ");
                SQL.Append("     ID_PALESTRA = " + obj.ID_PALESTRA.ToString());

                DbCommand updateT_PALESTRA = database.GetSqlStringCommand(SQL.ToString());

                updateT_PALESTRA.CommandText = SQL.ToString();

                database.ExecuteNonQuery(updateT_PALESTRA, DALConnection.transaction);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Alterar[]

        public void alterar(T_PALESTRA[] obj)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();
                DbCommand[] updateT_PALESTRA = new DbCommand[obj.Length];

                for (int index = 0; index < obj.Length; index++)
                {
                    T_PALESTRA o = obj[index];
                    SQL = new StringBuilder();
                    SQL.Append(" UPDATE T_PALESTRA SET ");
                    SQL.Append("    ID_CASA_ESPIRITA = :ID_CASA_ESPIRITA , ");
                    SQL.Append("    DT_PALESTRA = :DT_PALESTRA , ");
                    SQL.Append("    TX_TEMA = :TX_TEMA , ");
                    SQL.Append("    TX_ORADOR = :TX_ORADOR ");
                    SQL.Append(" WHERE ");
                    SQL.Append("     ID_PALESTRA = :ID_PALESTRA  ");

                    updateT_PALESTRA[index] = database.GetSqlStringCommand(SQL.ToString());

                    database.AddInParameter(updateT_PALESTRA[index], ":ID_PALESTRA", DbType.Int64, o.ID_PALESTRA);
                    database.AddInParameter(updateT_PALESTRA[index], ":ID_CASA_ESPIRITA", DbType.Int64, o.ID_CASA_ESPIRITA);
                    database.AddInParameter(updateT_PALESTRA[index], ":DT_PALESTRA", DbType.Object, o.DT_PALESTRA);
                    database.AddInParameter(updateT_PALESTRA[index], ":TX_TEMA", DbType.String, o.TX_TEMA);
                    database.AddInParameter(updateT_PALESTRA[index], ":TX_ORADOR", DbType.String, o.TX_ORADOR);

                    updateT_PALESTRA[index].CommandText = SQL.ToString();
                }

                try
                {
                    foreach (DbCommand cmd in updateT_PALESTRA)
                    {
                        database.ExecuteNonQuery(cmd, DALConnection.transaction);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Excluir

        public void excluir(T_PALESTRA obj)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();

                SQL.Append(" DELETE FROM T_PALESTRA ");
                SQL.Append(" WHERE ");
                SQL.Append("     ID_PALESTRA = " + obj.ID_PALESTRA.ToString());

                DbCommand deleteT_PALESTRA = database.GetSqlStringCommand(SQL.ToString());

                deleteT_PALESTRA.CommandText = SQL.ToString();

                database.ExecuteNonQuery(deleteT_PALESTRA, DALConnection.transaction);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region Excluir[]

        public void excluir(T_PALESTRA[] obj)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();
                DbCommand[] deleteT_PALESTRA = new DbCommand[obj.Length];

                for (int index = 0; index < obj.Length; index++)
                {
                    T_PALESTRA o = obj[index];
                    SQL = new StringBuilder();
                    SQL.Append(" DELETE FROM T_PALESTRA ");
                    SQL.Append(" WHERE ");
                    SQL.Append("     ID_PALESTRA = :ID_PALESTRA  ");

                    deleteT_PALESTRA[index] = database.GetSqlStringCommand(SQL.ToString());

                    database.AddInParameter(deleteT_PALESTRA[index], ":ID_PALESTRA", DbType.Int64, o.ID_PALESTRA);

                    deleteT_PALESTRA[index].CommandText = SQL.ToString();
                }

                try
                {
                    foreach (DbCommand cmd in deleteT_PALESTRA)
                    {
                        database.ExecuteNonQuery(cmd, DALConnection.transaction);
                    }
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region ObterPorPK

        public T_PALESTRA obterPorPK(long pID_PALESTRA)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();

                SQL.Append(" SELECT * FROM T_PALESTRA ");
                SQL.Append(" WHERE ");
                SQL.Append("  ID_PALESTRA = " + pID_PALESTRA.ToString());

                DbCommand consultaT_PALESTRA = database.GetSqlStringCommand(SQL.ToString());
                consultaT_PALESTRA.CommandText = SQL.ToString();

                IDataReader reader = database.ExecuteReader(consultaT_PALESTRA, DALConnection.transaction);
                T_PALESTRA obj = null;

                if (reader.Read())
                {
                    obj = new T_PALESTRA();
                    if (!reader.IsDBNull(reader.GetOrdinal("ID_PALESTRA")))
                    {
                        obj.ID_PALESTRA = Int64.Parse(reader["ID_PALESTRA"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("ID_CASA_ESPIRITA")))
                    {
                        obj.ID_CASA_ESPIRITA = Int64.Parse(reader["ID_CASA_ESPIRITA"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("DT_PALESTRA")))
                    {
                        obj.DT_PALESTRA = DateTime.Parse(reader["DT_PALESTRA"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("HR_PALESTRA")))
                    {
                        obj.HR_PALESTRA = DateTime.Parse(reader["HR_PALESTRA"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_TEMA")))
                    {
                        obj.TX_TEMA = reader["TX_TEMA"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_ORADOR")))
                    {
                        obj.TX_ORADOR = reader["TX_ORADOR"].ToString();
                    }
                }
                reader.Dispose();
                consultaT_PALESTRA.Dispose();

                return obj;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        #endregion

        #region ListarTodos

        public List<T_PALESTRA> listarTodos()
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();

                SQL.Append(" SELECT * FROM T_PALESTRA ORDER BY DT_PALESTRA desc, HR_PALESTRA desc, TX_TEMA");

                DbCommand consultaT_PALESTRA = database.GetSqlStringCommand(SQL.ToString());
                consultaT_PALESTRA.CommandText = SQL.ToString();

                IDataReader reader = database.ExecuteReader(consultaT_PALESTRA, DALConnection.transaction);
                List<T_PALESTRA> lista = new List<T_PALESTRA>();
                T_PALESTRA obj = null;

                while (reader.Read())
                {
                    obj = new T_PALESTRA();
                    if (!reader.IsDBNull(reader.GetOrdinal("ID_PALESTRA")))
                    {
                        obj.ID_PALESTRA = Int64.Parse(reader["ID_PALESTRA"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("ID_CASA_ESPIRITA")))
                    {
                        obj.ID_CASA_ESPIRITA = Int64.Parse(reader["ID_CASA_ESPIRITA"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("DT_PALESTRA")))
                    {
                        obj.DT_PALESTRA = DateTime.Parse(reader["DT_PALESTRA"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("HR_PALESTRA")))
                    {
                        obj.HR_PALESTRA = DateTime.Parse(reader["HR_PALESTRA"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_TEMA")))
                    {
                        obj.TX_TEMA = reader["TX_TEMA"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_ORADOR")))
                    {
                        obj.TX_ORADOR = reader["TX_ORADOR"].ToString();
                    }
                    lista.Add(obj);
                }
                reader.Dispose();
                consultaT_PALESTRA.Dispose();

                return lista;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public List<VOT_PALESTRA> consultar(string palestrante, int idCasaEspirita, DateTime data)
        {
            StringBuilder SQL = new StringBuilder();
            try
            {
                Database database = getDatabase();

                SQL.Append(" SELECT A.ID_PALESTRA AS ID_PALESTRANTE,A.ID_CASA_ESPIRITA AS ID_CASA_ESPIRITA, A.HR_PALESTRA AS HR_PALESTRA,");
                SQL.Append(" A.DT_PALESTRA AS DT_PALESTRA, A.TX_TEMA AS TX_TEMA, A.TX_ORADOR AS TX_ORADOR, U.TX_NOME_INSTITUICAO AS TX_CASA_ESPIRITA FROM T_PALESTRA A, T_CASA_ESPIRITA U WHERE ");
                SQL.Append("A.ID_CASA_ESPIRITA = U.ID_CASA_ESPIRITA AND A.TX_ORADOR LIKE '" + palestrante + "%'");

                if (!idCasaEspirita.Equals(0))
                    SQL.Append(" AND A.ID_CASA_ESPIRITA = " + idCasaEspirita.ToString());

                if (data.ToString("dd/MM/yyyy") != "01/01/0001")
                    SQL.Append(" AND date_format(A.DT_PALESTRA, '%d/%m/%Y') = date_format(str_to_date('" + data.ToString() + "', '%d/%m/%Y'), '%d/%m/%Y')");

                SQL.Append(" ORDER BY A.DT_PALESTRA DESC, A.HR_PALESTRA DESC,A.TX_ORADOR ASC, U.TX_NOME_INSTITUICAO ASC");

                DbCommand consultaT_PALESTRA = database.GetSqlStringCommand(SQL.ToString());
                consultaT_PALESTRA.CommandText = SQL.ToString();

                IDataReader reader = database.ExecuteReader(consultaT_PALESTRA, DALConnection.transaction);
                List<VOT_PALESTRA> lista = new List<VOT_PALESTRA>();
                VOT_PALESTRA obj = null;

                while (reader.Read())
                {
                    obj = new VOT_PALESTRA();
                    if (!reader.IsDBNull(reader.GetOrdinal("ID_PALESTRANTE")))
                    {
                        obj.ID_PALESTRA = Int64.Parse(reader["ID_PALESTRANTE"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("ID_CASA_ESPIRITA")))
                    {
                        obj.ID_CASA_ESPIRITA = Int64.Parse(reader["ID_CASA_ESPIRITA"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("DT_PALESTRA")))
                    {
                        obj.DT_PALESTRA = DateTime.Parse(reader["DT_PALESTRA"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("HR_PALESTRA")))
                    {
                        obj.HR_PALESTRA = DateTime.Parse(reader["HR_PALESTRA"].ToString());
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_TEMA")))
                    {
                        obj.TX_TEMA = reader["TX_TEMA"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_ORADOR")))
                    {
                        obj.TX_ORADOR = reader["TX_ORADOR"].ToString();
                    }
                    if (!reader.IsDBNull(reader.GetOrdinal("TX_CASA_ESPIRITA")))
                    {
                        obj.TX_CASA_ESPIRITA = reader["TX_CASA_ESPIRITA"].ToString();
                    }
                    lista.Add(obj);
                }
                reader.Dispose();
                consultaT_PALESTRA.Dispose();

                return lista;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion

    }
}

